from flask import Flask, render_template, request, send_file
import os
import pandas as pd
from datetime import datetime

app = Flask(__name__)
app.config['UPLOAD_FOLDER'] = 'uploads'
app.config['RESULTS_FOLDER'] = 'results'
os.makedirs(app.config['UPLOAD_FOLDER'], exist_ok=True)
os.makedirs(app.config['RESULTS_FOLDER'], exist_ok=True)

def process_data(upload_path, start_date, end_date, selected_subjects, positive_rate, negative_rate):
    # 核心处理逻辑（根据readme要求实现）
    df = pd.read_excel(upload_path, sheet_name='Sheet1')
    
    # 筛选选定科目
    df_filtered = df[df['科目描述'].isin(selected_subjects)]
    
    # 创建中间表
    df_debit = df_filtered.copy()
    df_credit = df_filtered.copy()
    df_debit['借方金额'] = 0
    df_credit['贷方金额'] = 0
    
    # 合并表格
    df_combined = pd.concat([df_debit, df_credit])
    df_combined['差额'] = df_combined['借方金额'] - df_combined['贷方金额']
    
    # 生成日期范围
    date_range = pd.date_range(start=start_date, end=end_date)
    
    # 创建透视表
    pivot_table = pd.pivot_table(df_combined,
                                values='差额',
                                index='过账日期',
                                columns='项目WBS',
                                aggfunc='sum',
                                fill_value=0)
    
    # 重新索引到完整日期范围
    full_pivot = pivot_table.reindex(date_range, fill_value=0)
    
    # 添加项目WBS描述映射
    wbs_mapping = df_filtered[['项目WBS', '项目WBS描述']].drop_duplicates().set_index('项目WBS')
    full_pivot = full_pivot.T.merge(wbs_mapping, left_index=True, right_index=True, how='left')
    full_pivot = full_pivot.set_index('项目WBS描述', append=True).T
    
    # 计算累积差额
    cumulative_diff = full_pivot.cumsum()
    
    # 计算每日利息
    daily_interest = cumulative_diff.applymap(lambda x: x * positive_rate/365 if x > 0 else x * negative_rate/365)
    
    # 计算累积利息
    cumulative_interest = daily_interest.cumsum()
    
    # 生成结果文件
    timestamp = datetime.now().strftime("%Y%m%d%H%M%S")
    output_path = os.path.join(app.config['RESULTS_FOLDER'], f'result_{timestamp}.xlsx')
    
    with pd.ExcelWriter(output_path) as writer:
        full_pivot.T.to_excel(writer, sheet_name='每日差额表')
        cumulative_diff.T.to_excel(writer, sheet_name='差额累积表') 
        daily_interest.T.to_excel(writer, sheet_name='每日利息表')
        cumulative_interest.T.to_excel(writer, sheet_name='利息累积表')
    
    return output_path

@app.route('/', methods=['GET', 'POST'])
def index():
    if request.method == 'POST':
        # 处理文件上传
        if 'excel_file' not in request.files:
            return render_template('index.html', error="请选择要上传的文件")
            
        file = request.files['excel_file']
        if file.filename == '':
            return render_template('index.html', error="无效的文件名")
            
        if not file.filename.endswith('.xlsx'):
            return render_template('index.html', error="仅支持.xlsx格式文件")
            
        try:
            # 保存上传文件
            upload_path = os.path.join(app.config['UPLOAD_FOLDER'], file.filename)
            file.save(upload_path)
            
            # 解析元数据
            df = pd.read_excel(upload_path)
            min_date = df['过账日期'].min().strftime('%Y-%m-%d')
            max_date = df['过账日期'].max().strftime('%Y-%m-%d')
            subjects = df['科目描述'].unique().tolist()
            
            return render_template('params.html',
                                min_date=min_date,
                                max_date=max_date,
                                subjects=subjects,
                                upload_path=upload_path)
                                
        except Exception as e:
            return render_template('index.html', error=f"文件解析失败: {str(e)}")
    
    # 确保所有代码路径都有返回值
    return render_template('index.html')
@app.route('/params', methods=['POST'])
def handle_params():
    try:
        # 获取参数
        start_date = pd.to_datetime(request.form['start_date'])
        end_date = pd.to_datetime(request.form['end_date'])
        selected_subjects = request.form.getlist('selected_subjects')
        upload_path = request.form['upload_path']
        positive_rate = float(request.form['positive_rate']) / 100  # 转换为小数
        negative_rate = float(request.form['negative_rate']) / 100  # 转换为小数
        
        # 参数验证
        if start_date > end_date:
            raise ValueError("开始日期不能晚于结束日期")
            
        if not selected_subjects:
            raise ValueError("请至少选择一个科目")
        
        # 处理数据
        result_path = process_data(upload_path, start_date, end_date, selected_subjects, positive_rate, negative_rate)
        
        return render_template('result.html',
                            download_file=os.path.basename(result_path))
        
    except Exception as e:
        return render_template('params.html',
                            error=f"处理失败: {str(e)}",
                            min_date=request.form['min_date'],
                            max_date=request.form['max_date'],
                            subjects=eval(request.form['subjects']))

@app.route('/download/<filename>')
def download(filename):
    return send_file(os.path.join(app.config['RESULTS_FOLDER'], filename),
                     as_attachment=True,
                     download_name='计算结果.xlsx')

if __name__ == '__main__':
    app.run(debug=True)
